package com.hspedu.com.jdbc.batch;

import com.hspedu.com.jdbc.utils.JDBCUtils;
import org.junit.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * @author DL5O
 * @version 1.0
 * 演示java的批处理
 */
public class Batch_ {

    public static void main(String[] args) throws SQLException {
        System.out.println("====传统方式进行记录的添加====");
        noBatch();//2329 毫秒

        System.out.println("====批处理方式进行记录的添加====");
        batch();//73 毫秒
    }

    //传统方法，添加5000条数据到admin2表中
    @Test
    public static void noBatch() throws SQLException {
        Connection connection = JDBCUtils.getConnection();
        PreparedStatement preparedStatement = null;
        String sql = "insert into admin2 values(null,?,?)";
        long start = 0;
        long end = 0;
        try {
            preparedStatement = connection.prepareStatement(sql);
            start = System.currentTimeMillis();
            for (int i = 1; i <= 5000; i++) {
                preparedStatement.setString(1, "用户 " + i);
                preparedStatement.setString(2, "1234");
                preparedStatement.executeUpdate();
            }
            System.out.println("执行成功");
            end = System.currentTimeMillis();
            System.out.println("运行时间:" + (end - start));
        } catch (SQLException throwables) {
            System.out.println("执行出错"); //运行时间:2570
            throwables.printStackTrace();
        } finally {
            JDBCUtils.close(null, preparedStatement, connection);
        }
    }

    //使用批量方式添加数据
    @Test
    public static void batch(){
        Connection connection = JDBCUtils.getConnection();
        PreparedStatement preparedStatement = null;
        String sql = "insert into admin2 values(null,?,?)";
        long start = 0;
        long end = 0;
        try {
            preparedStatement = connection.prepareStatement(sql);
            start = System.currentTimeMillis();
            for (int i = 1; i <= 5000; i++) {
                preparedStatement.setString(1, "用户 " + i);
                preparedStatement.setString(2, "1234");
                preparedStatement.addBatch();
                //当有1000条记录时，再批量执行
                if( i%1000 == 0){
                    preparedStatement.executeBatch();
                    //清空一把
                    preparedStatement.clearBatch();
                }
            }
            System.out.println("执行成功");
            end = System.currentTimeMillis();
            System.out.println("运行时间:" + (end - start));
        } catch (SQLException throwables) {
            System.out.println("执行出错"); //运行时间:2570
            throwables.printStackTrace();
        } finally {
            JDBCUtils.close(null, preparedStatement, connection);
        }
    }
}
